﻿
CREATE PROCEDURE [dbo].[ToolIDSearchByKeyword]
	@keywords varchar(100), 
	@script bit,
	@files bit,
	@notes bit,
	@allauthor bit,
	@author authortabletype readonly,
	@alltags bit,
	@tags tagtabletype readonly,
	@allowner bit,
	@owner toolownertabletype readonly

AS
BEGIN

	SET NOCOUNT ON;

	DECLARE @SearchResult TABLE (ToolID int, [Rank] int);

	WITH Search_CTE (ToolID, SearchTable, [Rank])
	AS
    (  
	SELECT tool.toolID, 'Tool' AS SearchTable, 1 AS [rank] FROM dbo.Tool
		WHERE 
		    @keywords = '%' AND /* wildcard search */
			(@allauthor=1 OR Author IN (select author from @author)) AND
			(@alltags=1 OR tool.toolid IN (SELECT toolid FROM tooltag WHERE tag IN (select tag from @tags))) AND
			(@allowner=1 OR tool.addedby IN (SELECT toolowner AS addedby FROM @owner))

	UNION ALL

	SELECT tool.toolID, 'Tool' AS SearchTable, FTT.[rank] FROM dbo.Tool
		INNER JOIN FREETEXTTABLE(Tool,([Title],[Description]),@keywords) AS FTT
		ON (tool.toolID = FTT.[Key])
		WHERE 
			(@allauthor=1 OR Author IN (select author from @author)) AND
			(@alltags=1 OR tool.toolid IN (SELECT toolid FROM tooltag WHERE tag IN (select tag from @tags))) AND
			(@allowner=1 OR tool.addedby IN (SELECT toolowner AS addedby FROM @owner))

	UNION ALL

	SELECT  tool.toolid, 'Script' AS SearchTable, FTT.[rank] FROM dbo.Tool
			INNER JOIN FREETEXTTABLE(ToolScript,([script], [scriptnote]),@keywords) AS FTT
		ON (tool.toolID = FTT.[Key])
		WHERE   
			@script = 1
			AND (@allauthor=1 OR Author IN (select author from @author))
			AND (@alltags=1 OR tool.toolid IN (SELECT toolid FROM tooltag WHERE tag IN (select tag from @tags)))	
			AND (@allowner=1 OR tool.addedby IN (SELECT toolowner AS addedby FROM @owner))
	
	UNION ALL
  
	SELECT  tool.toolid, 'Notes' AS SearchTable, FTT.[rank] FROM dbo.Tool
			INNER JOIN FREETEXTTABLE(ToolNote,([note]),@keywords) AS FTT
		ON (tool.toolID = FTT.[Key])
		WHERE   
			@script = 1
			AND (@allauthor=1 OR Author IN (select author from @author))
			AND (@alltags=1 OR tool.toolid IN (SELECT toolid FROM tooltag WHERE tag IN (select tag from @tags)))	
			AND (@allowner=1 OR tool.addedby IN (SELECT toolowner AS addedby FROM @owner))
	
	UNION ALL    

	 SELECT tool.toolid, 'Files' AS SearchTable, FTT.[rank] from toolfilemap 
		inner join tool on tool.toolid = toolfilemap.toolid
		INNER JOIN ToolFile ON toolfilemap.stream_id = toolfile.stream_id
		INNER JOIN FREETEXTTABLE(ToolFile,([name],[file_stream]),@keywords) AS FTT
			ON (toolfile.path_locator = FTT.[Key])
		WHERE 
		(@allauthor=1 OR Author IN (select author from @author))	
		AND (@alltags=1 OR tool.toolid IN (SELECT toolid FROM tooltag WHERE tag IN (select tag from @tags)))
		AND (@allowner=1 OR tool.addedby IN (SELECT toolowner AS addedby FROM @owner))
	)
    INSERT  INTO @SearchResult
            SELECT  ToolID ,
                    SUM(RANK)
            FROM    Search_CTE
            GROUP BY ToolID
            ORDER BY SUM([rank]) DESC;

    SELECT  Tool.ToolID, AuthorID, Author, Title, [Description], DescriptionRich, Compatibility, ProductionSafe, ToolAdded, AddedBy, SR.[Rank]
    FROM    Tool
            INNER JOIN @SearchResult SR ON SR.ToolID = Tool.ToolID
    ORDER BY SR.[Rank] DESC

END